package com.picvik.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.picvik.util.MyLog;

// Class that has functionality to implement content based filtering and recommendation of 
// discussions.
public class DiscussionRecommendationEngine {
	
	private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    
    
	// Returns a list of recommended discussion model object.
	// Use the discussion id and title of the model object and display a hyper-link.
	// Parameter: expects a list of tags currently in use by the user and based on that 
	// recommended discussion is returned.
public	ArrayList<DiscussionModel> getRecommendedDiscussions(String[] tags){
		
		ArrayList<DiscussionModel> recommendedDiscussions = new ArrayList<DiscussionModel>();
		
		String getRecommendedDiscussionsQuery ;
		
		try{
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			// If no tag names are passed give latest 50 general discussions
			if(tags == null || tags.length == 0){ 
				
				getRecommendedDiscussionsQuery = "SELECT pd.* FROM picvik_discussion pd WHERE pd.id IN "+
	            " ( SELECT ptd.discussion_id FROM picvik_tag_discussion ptd WHERE ptd.tag_id IN "+
			    " ( SELECT pt.tagid FROM picvik_tag pt WHERE pt.tag = " + "'General')" +		
	            ") ORDER BY pd.lastmodified DESC LIMIT 50;";
				
				MyLog.log(getRecommendedDiscussionsQuery);
				
				preparedStatement = connection.prepareStatement(getRecommendedDiscussionsQuery);
				resultSet = preparedStatement.executeQuery();
				
				while(resultSet.next()){
					DiscussionModel discussionModel = new DiscussionModel();
				
					discussionModel.setId(resultSet.getInt("id"));
					discussionModel.setTitle(resultSet.getString("title"));
					discussionModel.setDescription(resultSet.getString("description"));
					discussionModel.setOwner_id(resultSet.getInt("owner_id"));
					discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
					discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
					
					discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
					
					recommendedDiscussions.add(discussionModel);
				}	
				
			}else{ // else get recommendation based on the tags
				
				// Finally we need only 50 recommendations. If there are 10 tags then 50/10 = 5 recommendations per tag is returned.
				int limitPerTag = 50 / tags.length;
				
				for(String tag : tags){
				
					getRecommendedDiscussionsQuery = "SELECT pd.* FROM picvik_discussion pd WHERE pd.id IN "+
				            " ( SELECT ptd.discussion_id FROM picvik_tag_discussion ptd WHERE ptd.tag_id IN "+
						    " ( SELECT pt.tagid FROM picvik_tag pt WHERE pt.tag = '" + tag + "')" +		
				            ") ORDER BY pd.lastmodified DESC LIMIT " + limitPerTag +" ;";					
					
					MyLog.log(getRecommendedDiscussionsQuery);
					
					preparedStatement = connection.prepareStatement(getRecommendedDiscussionsQuery);
					resultSet = preparedStatement.executeQuery();
					
					while(resultSet.next()){
						DiscussionModel discussionModel = new DiscussionModel();
					
						discussionModel.setId(resultSet.getInt("id"));
						discussionModel.setTitle(resultSet.getString("title"));
						discussionModel.setDescription(resultSet.getString("description"));
						discussionModel.setOwner_id(resultSet.getInt("owner_id"));
						discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
						discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
						
						discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
						
						recommendedDiscussions.add(discussionModel);
					}	
				}				
			}
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}		
		
		return recommendedDiscussions;
	}
}
